﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using BusinessLogic;
using System.Data.SqlClient;
using System.Web.Security;

namespace DataAccessLogic
{
    public static class InstrumentDataAccess
    {
        private static Instrument BindRowToInstrument(DataRow row)
        {
            Instrument i = new Instrument();
            i.ID = DataAccess.CheckForGuidColumn(row, "ID");
            i.Model = DataAccess.CheckForStringColumn(row, "MODEL");            
            i.DateAdded = DataAccess.CheckForDateTimeColumn(row, "DATEADDED");
            i.DateChanged = DataAccess.CheckForDateTimeColumn(row, "DATECHANGED");
            i.AddedByID = DataAccess.CheckForGuidColumn(row, "ADDEDBYID");
            i.ChangedByID = DataAccess.CheckForGuidColumn(row, "CHANGEDBYID");

            InstrumentType it = new InstrumentType();
            it.ID = DataAccess.CheckForGuidColumn(row, "INSTRUMENTTYPECODEID");
            it.FillByID();
            i.InstrumentType = it;
            
            ManufacturerType mt = new ManufacturerType();
            mt.ID = DataAccess.CheckForGuidColumn(row, "MANUFACTURERTYPECODEID");
            mt.FillByID();
            i.ManufacturerType = mt;

            return i;
        }
        
        public static Guid Create(Instrument instrument)
        {
            Guid id = Guid.Empty;
            Guid providerUserKey = new Guid(Membership.GetUser().ProviderUserKey.ToString());
            string outputParameterName = "@ID";

            if (instrument != null)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                SqlParameter p = new SqlParameter();
                p.ParameterName = outputParameterName;
                p.DbType = System.Data.DbType.Guid;
                p.Direction = System.Data.ParameterDirection.Output;

                parms.Add(p);

                Object instrumentTypeID, manufacturerTypeID;

                try
                {
                    instrumentTypeID = instrument.InstrumentType.ID;
                }
                catch 
                {
                    instrumentTypeID = DBNull.Value;
                }
                                
                try
                {
                    manufacturerTypeID = instrument.ManufacturerType.ID;
                }
                catch 
                {
                    manufacturerTypeID = DBNull.Value;
                }


                parms.Add(new SqlParameter("@INSTRUMENTTYPECODEID", instrumentTypeID));
                parms.Add(new SqlParameter("@MANUFACTURERTYPECODEID", manufacturerTypeID));
                parms.Add(new SqlParameter("@MODEL", instrument.Model));                
                parms.Add(new SqlParameter("@ASPNETDBUSERID", providerUserKey));

                object obj = DataAccess.Create(StoredProcedures.USP_INSTRUMENT_CREATE, outputParameterName, parms);

                if (obj != null)
                {
                    id = new Guid(obj.ToString());
                }
            }

            return id;
        }

        private static List<Instrument> Get(string storedProcedure)
        {
            return Get(storedProcedure, null);
        }

        private static List<Instrument> Get(string storedProcedure, List<SqlParameter> parms)
        {
            List<Instrument> instruments = null;

            DataTable dt = DataAccess.Get(storedProcedure, parms);

            if (null != dt)
            {
                instruments = new List<Instrument>();

                foreach (DataRow row in dt.Rows)
                {
                    instruments.Add(BindRowToInstrument(row));
                }
            }

            return instruments;
        }
        
        public static List<Instrument> GetAll()
        {
            return Get(StoredProcedures.USP_INSTRUMENT_GETALL);
        }

        public static Instrument GetByID(Guid id)
        {
            Instrument instrument = null;

            if (id != Guid.Empty)
            {
                List<SqlParameter> parms = new List<SqlParameter>();
                parms.Add(new SqlParameter("@ID", id));

                List<Instrument> i = Get(StoredProcedures.USP_INSTRUMENT_GETBYID, parms);

                if (i != null && i.Count > 0)
                {
                    instrument = i[0];
                }
            }

            return instrument;
        }        

        public static DataTable SearchAll()
        {
            return DataAccess.Get(StoredProcedures.USP_INSTRUMENT_SEARCH);
        }
    }
}